USE [SONALIKA_BGk]
GO

/****** Object:  StoredProcedure [dbo].[SpSpareSalesInvoice]    Script Date: 04/08/2014 23:54:37 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SpSpareSalesInvoice]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SpSpareSalesInvoice]
GO

USE [SONALIKA_BGk]
GO

/****** Object:  StoredProcedure [dbo].[SpSpareSalesInvoice]    Script Date: 04/08/2014 23:54:37 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




CREATE PROC [dbo].[SpSpareSalesInvoice]-- 34
(
@INVOICE_ID INT
)
AS
BEGIN
SELECT CONVERT(VARCHAR(10),SPS.SALES_INVOICE_ID) AS INVOICE_NO
       ,replace(convert(NVARCHAR, INVOICE_DATE, 106), ' ', '-')AS INVOICE_DATE   
        ,INVOICE_DISCOUNT   
       ,INVOICE_VAT_PERCENT
       ,[SUB_TOTAL] as INVOICE_GRANDTOTAL
        ,SELL_TYPE
        ,CUSTOMER_NAME
       ,CUSTOMER_ADDRESS
       ,CONTACT_NO_1 AS CONTACT_NO
       ,SPARE_PART_CODE
       ,SPARE_PART_DESCRIPTION
       ,VILLAGE_NAME
       ,[QUANTITY]
       ,SPARE_RATE_VALUE AS RATE
       ,dbo.fNumToWords(INVOICE_GRANDTOTAL)
	 AS AMOUNT_IN_WORDS
       
 FROM SPARE_PURCHASES_SALES SPS
   LEFT OUTER JOIN SALES_INVOICES SI ON SI.SALES_INVOICE_ID=SPS.SALES_INVOICE_ID
     LEFT OUTER JOIN CUSTOMER C ON C.CUSTOMER_ID =SI.CUSTOMER_ID
       LEFT OUTER JOIN SPARE_PARTS SP ON SP.SPARE_PART_ID =SPS.SPARE_PART_ID 
        LEFT OUTER JOIN  VILLAGES V ON V.VILLAGE_ID=C.VILLAGE_ID
          LEFT OUTER JOIN SPARE_RATES SR ON SR.SPARE_PART_ID =SPS.SPARE_PART_ID AND SR.SUPPLIER_ID=SPS.SUPPLIER_ID
    
     
 WHERE SI.SALES_INVOICE_ID=@INVOICE_ID  AND SELL_TYPE IN(SELECT MASTER_ID FROM MASTER WHERE MASTER_VALUE='SPARE' OR MASTER_VALUE='LUBRICANT')
END



GO


